# -*- coding: utf-8 -*-
"""
Repository基类 - 修复版
"""
import mysql.connector # type: ignore
from mysql.connector import Error # type: ignore
from contextlib import contextmanager
from typing import Dict, List, Any, Optional, Tuple
import os
import re
import logging
import pymysql
from app.sql.database_config import execute_query, execute_update, execute_insert, DatabaseConfig

logger = logging.getLogger(__name__)

class BaseRepository:
    """数据库访问基类 - 支持表名参数"""
    
    def __init__(self, table_name: str = None):
        self.table_name = table_name
        self.db_config = {
            'host': os.getenv('DB_HOST', 'localhost'),
            'port': int(os.getenv('DB_PORT', 3306)),
            'user': os.getenv('DB_USER', 'root'),
            'password': os.getenv('DB_PASSWORD', '123456'),
            'database': os.getenv('DB_NAME', 'fastapi'),
            'charset': 'utf8mb4',
            'autocommit': True
        }
        self.logger = logging.getLogger(__name__)
        self.connection = None
        # 定义校对规则优先级
        self.preferred_collation = 'utf8mb4_unicode_ci'
    
    def get_connection(self):
        """获取数据库连接"""
        if not self.connection:
            try:
                self.connection = pymysql.connect(
                    host=DatabaseConfig.HOST,
                    port=DatabaseConfig.PORT,
                    user=DatabaseConfig.USER,
                    password=DatabaseConfig.PASSWORD,
                    database=DatabaseConfig.DATABASE,
                    charset=DatabaseConfig.CHARSET,
                    autocommit=DatabaseConfig.AUTocommit
                )
                logger.info("数据库连接创建成功")
            except Exception as e:
                logger.error(f"数据库连接失败: {str(e)}")
                raise
        return self.connection
    
    def execute_query(self, query: str, params: List = None) -> List[Dict]:
        """执行查询并返回字典列表"""
        connection = None
        cursor = None
        try:
            connection = self.get_connection()
            cursor = connection.cursor(pymysql.cursors.DictCursor)
            
            logger.info(f"执行查询: {query}")
            logger.info(f"参数: {params}")
            
            cursor.execute(query, params or [])
            results = cursor.fetchall()
            
            return results
            
        except Exception as e:
            logger.error(f"查询执行失败: {str(e)}")
            raise
        finally:
            if cursor:
                cursor.close()
    
    def execute_single_query(self, query: str, params: List = None) -> Optional[Dict]:
        """执行查询并返回单个字典结果"""
        connection = None
        cursor = None
        try:
            connection = self.get_connection()
            cursor = connection.cursor(pymysql.cursors.DictCursor)
            
            logger.info(f"执行查询: {query}")
            logger.info(f"参数: {params}")
            
            cursor.execute(query, params or [])
            result = cursor.fetchone()
            
            return result
            
        except Exception as e:
            logger.error(f"查询执行失败: {str(e)}")
            raise
        finally:
            if cursor:
                cursor.close()
    
    def execute_update(self, query: str, params: List = None) -> int:
        """执行更新操作并返回影响的行数"""
        connection = None
        cursor = None
        try:
            connection = self.get_connection()
            cursor = connection.cursor()
            
            logger.info(f"执行更新: {query}")
            logger.info(f"参数: {params}")
            
            cursor.execute(query, params or [])
            connection.commit()
            
            affected_rows = cursor.rowcount
            return affected_rows
            
        except Exception as e:
            logger.error(f"更新执行失败: {str(e)}")
            if connection:
                connection.rollback()
            raise
        finally:
            if cursor:
                cursor.close()
    
    def execute_insert(self, query: str, params: List = None) -> int:
        """执行插入操作并返回插入的ID"""
        connection = None
        cursor = None
        try:
            connection = self.get_connection()
            cursor = connection.cursor()
            
            logger.info(f"执行插入: {query}")
            logger.info(f"参数: {params}")
            
            cursor.execute(query, params or [])
            connection.commit()
            
            last_id = cursor.lastrowid
            return last_id
            
        except Exception as e:
            logger.error(f"插入执行失败: {str(e)}")
            if connection:
                connection.rollback()
            raise
        finally:
            if cursor:
                cursor.close()
    
    def get_count(self, query: str, params: Optional[Tuple] = None) -> int:
        """获取记录数"""
        try:
            result = execute_query(query, params)
            if result and len(result) > 0:
                return result[0].get('count', 0)
            return 0
        except Exception as e:
            logger.error(f"获取记录数失败: {e}")
            return 0
    
    def find_by_id(self, id_field: str, id_value: Any) -> Optional[Dict]:
        """根据ID查找记录"""
        if not self.table_name:
            return None
        
        query = f"SELECT * FROM {self.table_name} WHERE {id_field} = %s"
        result = self.execute_query(query, [id_value], fetchall=False)
        return result
    def execute_custom_query(self, query: str, params: tuple = None):
        """执行自定义查询"""
        try:
            import mysql.connector
            connection = mysql.connector.connect(**self.db_config)
            cursor = connection.cursor(dictionary=True)
            
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)
            
            result = cursor.fetchall()
            cursor.close()
            connection.close()
            
            return result if result else []
        except Exception as e:
            print(f"查询错误: {e}")
            return []
    
    def execute_query(self, query: str, params: tuple = None):
        """执行查询（别名）"""
        return self.execute_custom_query(query, params)
    
    def count_query(self, query: str, params: tuple = None, *args, **kwargs):
        """计数查询"""
        try:
            import mysql.connector
            connection = mysql.connector.connect(**self.db_config)
            cursor = connection.cursor()
            
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)
            
            result = cursor.fetchone()
            cursor.close()
            connection.close()
            
            return result[0] if result else 0
        except Exception as e:
            print(f"计数错误: {e}")
            return 0

    def execute_custom_query(self, query: str, params: tuple = None, *args, **kwargs):
        """执行自定义查询"""
        try:
            import mysql.connector
            connection = mysql.connector.connect(**self.db_config)
            cursor = connection.cursor(dictionary=True)
            
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)
            
            result = cursor.fetchall()
            cursor.close()
            connection.close()
            
            return result if result else []
        except Exception as e:
            print(f"查询错误: {e}")
            return []
    
    def execute_query(self, query: str, params: tuple = None, *args, **kwargs):
        """执行查询"""
        return self.execute_custom_query(query, params, *args, **kwargs)
    
    def count_query(self, query: str, params: tuple = None, *args, **kwargs):
        """计数查询"""
        try:
            import mysql.connector
            connection = mysql.connector.connect(**self.db_config)
            cursor = connection.cursor()
            
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)
            
            result = cursor.fetchone()
            cursor.close()
            connection.close()
            
            return result[0] if result else 0
        except Exception as e:
            print(f"计数错误: {e}")
            return 0

    def auto_fix_collation(self, query: str) -> str:
        """自动修复校对规则冲突"""
        try:
            # 检测是否包含JOIN操作
            if not re.search(r'\bJOIN\b', query, re.IGNORECASE):
                return query
            
            # 修复JOIN条件 - 更精确的模式匹配
            join_patterns = [
                # LEFT JOIN pattern
                (r'(LEFT\s+JOIN\s+\w+\s+\w+\s+ON\s+\w+\.\w+\s*=\s*\w+\.\w+)', 
                 lambda m: self._fix_join_condition(m.group(1))),
                
                # RIGHT JOIN pattern
                (r'(RIGHT\s+JOIN\s+\w+\s+\w+\s+ON\s+\w+\.\w+\s*=\s*\w+\.\w+)', 
                 lambda m: self._fix_join_condition(m.group(1))),
                
                # INNER JOIN pattern
                (r'(INNER\s+JOIN\s+\w+\s+\w+\s+ON\s+\w+\.\w+\s*=\s*\w+\.\w+)', 
                 lambda m: self._fix_join_condition(m.group(1))),
                
                # JOIN pattern (default INNER)
                (r'(JOIN\s+\w+\s+\w+\s+ON\s+\w+\.\w+\s*=\s*\w+\.\w+)', 
                 lambda m: self._fix_join_condition(m.group(1))),
            ]
            
            for pattern, replacement in join_patterns:
                query = re.sub(pattern, replacement, query, flags=re.IGNORECASE)
            
            return query
            
        except Exception as e:
            self.logger.warning(f"自动修复失败: {e}")
            return query
    
    def _fix_join_condition(self, join_clause: str) -> str:
        """修复JOIN条件"""
        return re.sub(
            r'(\w+\.\w+\s*=\s*\w+\.\w+)', 
            r'\1 COLLATE ' + self.preferred_collation, 
            join_clause, 
            flags=re.IGNORECASE
        )
    
    def execute_query(self, query: str, params: Optional[Tuple] = None) -> List[Dict[str, Any]]:
        """执行查询 - 自动处理校对规则冲突"""
        try:
            # 自动修复校对规则冲突
            fixed_query = self.auto_fix_collation(query)
            
            # 记录修复过程
            if query != fixed_query:
                self.logger.info(f"校对规则已自动修复:")
                self.logger.info(f"原始查询: {query}")
                self.logger.info(f"修复后查询: {fixed_query}")
            
            # 执行查询
            return self._execute_raw_query(fixed_query, params)
            
        except Exception as e:
            if "collation" in str(e).lower():
                self.logger.warning(f"校对规则冲突，尝试备用方案: {e}")
                return self._execute_fallback_query(query, params)
            else:
                self.logger.error(f"查询执行失败: {e}")
                raise
    
    def _execute_raw_query(self, query: str, params: Optional[Tuple] = None) -> List[Dict[str, Any]]:
        """执行原始查询"""
        connection = self.get_connection()
        cursor = connection.cursor(pymysql.cursors.DictCursor)
        
        try:
            cursor.execute(query, params)
            result = cursor.fetchall()
            return result
        finally:
            cursor.close()
    
    def _execute_fallback_query(self, query: str, params: Optional[Tuple] = None) -> List[Dict[str, Any]]:
        """备用查询方案"""
        try:
            fallback_query = self._convert_join_to_subquery(query)
            return self._execute_raw_query(fallback_query, params)
        except Exception as e:
            self.logger.error(f"备用查询也失败: {e}")
            raise
    
    def _convert_join_to_subquery(self, query: str) -> str:
        """将JOIN查询转换为子查询"""
        # 提取主表
        main_table_match = re.search(r'FROM\s+(\w+)\s+(\w+)', query, re.IGNORECASE)
        if not main_table_match:
            return query
        
        main_table = main_table_match.group(1)
        main_alias = main_table_match.group(2)
        
        # 提取JOIN信息
        join_matches = re.findall(r'JOIN\s+(\w+)\s+(\w+)\s+ON\s+(\w+\.\w+)\s*=\s*(\w+\.\w+)', query, re.IGNORECASE)
        
        # 构建子查询
        select_parts = [f"SELECT {main_alias}.*"]
        
        for join_table, join_alias, left_field, right_field in join_matches:
            if left_field.startswith(main_alias):
                foreign_key = left_field
                target_table = join_table
                target_field = right_field.split('.')[1]
            else:
                foreign_key = right_field
                target_table = join_table
                target_field = left_field.split('.')[1]
            
            select_parts.append(
                f"(SELECT {target_field} FROM {target_table} WHERE id = {foreign_key}) as {target_table}_name"
            )
        
        # 构建完整查询
        from_clause = f"FROM {main_table} {main_alias}"
        where_clause = ""
        
        where_match = re.search(r'WHERE\s+(.+)', query, re.IGNORECASE)
        if where_match:
            where_clause = f"WHERE {where_match.group(1)}"
        
        order_limit = ""
        order_match = re.search(r'(ORDER BY.+?)(?:LIMIT|$)', query, re.IGNORECASE | re.DOTALL)
        if order_match:
            order_limit = order_match.group(1)
        
        limit_match = re.search(r'(LIMIT.+?)$', query, re.IGNORECASE)
        if limit_match:
            order_limit += f" {limit_match.group(1)}"
        
        return f"{', '.join(select_parts)} {from_clause} {where_clause} {order_limit}".strip()
    
    def close_connection(self):
        """关闭数据库连接"""
        if self.connection:
            try:
                self.connection.close()
                logger.info("数据库连接已关闭")
            except Exception as e:
                logger.error(f"关闭数据库连接失败: {str(e)}")
            finally:
                self.connection = None
    
    def __del__(self):
        """析构函数，确保连接被关闭"""
        self.close_connection()
